{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b9261e7f-2a1f-4df0-98f7-1b350c849736",
   "metadata": {},
   "source": [
    "## A01_Explore the data and choose the KPI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02483f8b-9aa3-4986-98ae-dd7e0ba46907",
   "metadata": {},
   "source": [
    "### 1. init spark session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c6320c4b-f038-469d-992d-76901a5737a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4ac6681b-77f5-4fd3-98a5-c689e1ca76d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName(\"unemployment data\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e62b348a-b4d6-4e71-b6c2-cdbb022c9c71",
   "metadata": {},
   "source": [
    "### 2. read the dataset file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ee962a9b-3a28-484d-9086-8a9fd98242bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dir = \"./Landing Zone/unemployment\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "468b2ba9-5ea0-4097-bcb3-62b6e3694554",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.json(data_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b7f68e6-8b04-493c-94ff-cb69cb791808",
   "metadata": {},
   "source": [
    "### 3. show dataframe datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "284d3017-e4a4-4ea5-9408-6549d3c10de1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-------+\n",
      "|               error|                help|              result|success|\n",
      "+--------------------+--------------------+--------------------+-------+\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|                NULL|https://opendata-...|{{/api/action/dat...|   true|\n",
      "|{Not Found Error,...|https://opendata-...|                NULL|  false|\n",
      "+--------------------+--------------------+--------------------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97270dbf-ad8e-4b39-9e1a-50b9787949f6",
   "metadata": {},
   "source": [
    "#### show the dataframe schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "613569dd-72c5-42d8-8f1d-9b69202124c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- error: struct (nullable = true)\n",
      " |    |-- __type: string (nullable = true)\n",
      " |    |-- message: string (nullable = true)\n",
      " |-- help: string (nullable = true)\n",
      " |-- result: struct (nullable = true)\n",
      " |    |-- _links: struct (nullable = true)\n",
      " |    |    |-- next: string (nullable = true)\n",
      " |    |    |-- start: string (nullable = true)\n",
      " |    |-- fields: array (nullable = true)\n",
      " |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |-- id: string (nullable = true)\n",
      " |    |    |    |-- type: string (nullable = true)\n",
      " |    |-- records: array (nullable = true)\n",
      " |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |-- Any: string (nullable = true)\n",
      " |    |    |    |-- Codi_Barri: string (nullable = true)\n",
      " |    |    |    |-- Codi_Districte: string (nullable = true)\n",
      " |    |    |    |-- Demanda_ocupacio: string (nullable = true)\n",
      " |    |    |    |-- Demanda_ocupació: string (nullable = true)\n",
      " |    |    |    |-- Mes: string (nullable = true)\n",
      " |    |    |    |-- Nom_Barri: string (nullable = true)\n",
      " |    |    |    |-- Nom_Districte: string (nullable = true)\n",
      " |    |    |    |-- Nombre: string (nullable = true)\n",
      " |    |    |    |-- Sexe: string (nullable = true)\n",
      " |    |    |    |-- _id: long (nullable = true)\n",
      " |    |-- resource_id: string (nullable = true)\n",
      " |    |-- total: long (nullable = true)\n",
      " |-- success: boolean (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8918c5e9-31fd-44b7-a719-6877a3331806",
   "metadata": {},
   "source": [
    "### 4. show result fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d337df19-d77d-4ccd-9443-084a52ffe006",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import explode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0fcff3aa-1eff-499b-a6cd-a6e88ee7e029",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|                 col|\n",
      "+--------------------+\n",
      "|         {_id, int4}|\n",
      "|      {Any, numeric}|\n",
      "|      {Mes, numeric}|\n",
      "|{Codi_Districte, ...|\n",
      "|{Nom_Districte, t...|\n",
      "|{Codi_Barri, nume...|\n",
      "|   {Nom_Barri, text}|\n",
      "|        {Sexe, text}|\n",
      "|{Demanda_ocupació...|\n",
      "|   {Nombre, numeric}|\n",
      "|         {_id, int4}|\n",
      "|      {Any, numeric}|\n",
      "|      {Mes, numeric}|\n",
      "|{Codi_Districte, ...|\n",
      "|{Nom_Districte, t...|\n",
      "|{Codi_Barri, nume...|\n",
      "|   {Nom_Barri, text}|\n",
      "|        {Sexe, text}|\n",
      "|{Demanda_ocupació...|\n",
      "|   {Nombre, numeric}|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(explode(\"result.fields\")).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "925b8f73-84c5-471a-a6dd-d99f92bebbe9",
   "metadata": {},
   "source": [
    "### 5. show result records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e9681bcc-0952-4124-843f-7308d6f3e055",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+----------+--------------+----------------+----------------+---+--------------------+-------------+------+-----+---+\n",
      "| Any|Codi_Barri|Codi_Districte|Demanda_ocupacio|Demanda_ocupació|Mes|           Nom_Barri|Nom_Districte|Nombre| Sexe|_id|\n",
      "+----+----------+--------------+----------------+----------------+---+--------------------+-------------+------+-----+---+\n",
      "|2016|         1|             1|            NULL|  Atur registrat|  1|            el Raval| Ciutat Vella|  2431|Homes|  1|\n",
      "|2016|         2|             1|            NULL|  Atur registrat|  1|      el Barri Gòtic| Ciutat Vella|   588|Homes|  2|\n",
      "|2016|         3|             1|            NULL|  Atur registrat|  1|      la Barceloneta| Ciutat Vella|   637|Homes|  3|\n",
      "|2016|         4|             1|            NULL|  Atur registrat|  1|Sant Pere, Santa ...| Ciutat Vella|   878|Homes|  4|\n",
      "|2016|         5|             2|            NULL|  Atur registrat|  1|       el Fort Pienc|     Eixample|   693|Homes|  5|\n",
      "+----+----------+--------------+----------------+----------------+---+--------------------+-------------+------+-----+---+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(explode(\"result.records\").alias(\"record\")).select(\"record.*\").show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a61b7f05-7ab4-4ac9-a3d3-ba3957cff84f",
   "metadata": {},
   "source": [
    "### 6. conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d76c4186-057a-44a6-879a-5974efa12c39",
   "metadata": {},
   "source": [
    "This dataset describes the registered unemployment numbers by gender for different districts and neighborhoods in Barcelona"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c54dac2-067d-4062-b931-21e79aecf088",
   "metadata": {},
   "source": [
    "Based on this dataset, the following analysis objectives could be considered:\n",
    "\n",
    "- Gender Unemployment Comparison: Compare unemployment rates between different genders and analyze the disparities in unemployment rates for males and females across different regions.\n",
    "\n",
    "- Regional Unemployment Comparison: Analyze unemployment rates across various districts and neighborhoods to identify areas with higher and lower unemployment rates.\n",
    "\n",
    "- Time Series Analysis: If data from multiple time points are available, analyze the trends in unemployment rates over time to identify seasonal patterns and long-term trends.\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
